



<!DOCTYPE html>
<!--[if IE 8]><html class="no-js lt-ie9" lang="en" > <![endif]-->
<!--[if gt IE 8]><!--> <html class="no-js" lang="en" > <!--<![endif]-->
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  
  <title>Inefficient database queries &mdash; Python Anti-Patterns  documentation</title>
  

  
  

  

  
  
    

  

  
  
    <link rel="stylesheet" href="../../../_static/css/theme.css" type="text/css" />
  

  
    <link rel="stylesheet" href="../../../_static/css/ribbon.css" type="text/css" />
  
    <link rel="stylesheet" href="../../../_static/css/font-awesome-4.1.0/css/font-awesome.min.css" type="text/css" />
  
    <link rel="stylesheet" href="../../../_static/css/menu.css" type="text/css" />
  
        <link rel="index" title="Index"
              href="../../../genindex.html"/>
        <link rel="search" title="Search" href="../../../search.html"/>
    <link rel="top" title="Python Anti-Patterns  documentation" href="../../../index.html"/>
        <link rel="up" title="Performance" href="index.html"/>
        <link rel="next" title="Migration to 1.8" href="../../1.8/migration/index.html"/>
        <link rel="prev" title="Performance" href="index.html"/> 

</head>

<body class="wy-body-for-nav" role="document">

  <div class="wy-grid-for-nav">

    
    <nav data-toggle="wy-nav-shift" class="wy-nav-side">
      <div class="wy-side-nav-search">
        <a href="../../../index.html"> Python Anti-Patterns</a>
        <div role="search">
  <form id ="rtd-search-form" class="wy-form" action="../../../search.html" method="get">
    <input type="text" name="q" placeholder="Search docs" />
    <input type="hidden" name="check_keywords" value="yes" />
    <input type="hidden" name="area" value="default" />
  </form>
</div>
      </div>


      <div class="wy-menu wy-menu-vertical" data-spy="affix" role="navigation" aria-label="main navigation">
        
        
            <ul class="current">
<li class="toctree-l1"><a class="reference internal" href="../../../correctness/index.html"><i class="fa fa-check"></i> Correctness</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../maintainability/index.html"><i class="fa fa-puzzle-piece"></i> Maintainability</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../readability/index.html"><i class="fa fa-eye"></i> Readability</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../security/index.html"><i class="fa fa-lock"></i> Security</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../performance/index.html"><i class="fa fa-dashboard"></i> Performance</a></li>
<li class="toctree-l1 current"><a class="reference internal" href="../../index.html"><i class="fa fa-book"></i> Django</a><ul class="current">
<li class="toctree-l2"><a class="reference internal" href="../maintainability/index.html"><i class="fa fa-puzzle-piece"></i> Maintainability</a></li>
<li class="toctree-l2"><a class="reference internal" href="../security/index.html"><i class="fa fa-lock"></i> Security</a></li>
<li class="toctree-l2"><a class="reference internal" href="../correctness/index.html"><i class="fa fa-check"></i> Correctness</a></li>
<li class="toctree-l2 current"><a class="reference internal" href="index.html"><i class="fa fa-dashboard"></i> Performance</a><ul class="current">
<li class="toctree-l3 current"><a class="current reference internal" href="#">Inefficient database queries</a></li>
</ul>
</li>
<li class="toctree-l2"><a class="reference internal" href="../../1.8/migration/index.html"><i class="fa fa-magic"></i> Migration to 1.8</a></li>
</ul>
</li>
</ul>

        
      </div>
      &nbsp;

    </nav>

    <section data-toggle="wy-nav-shift" class="wy-nav-content-wrap">

      
      <nav class="wy-nav-top" role="navigation" aria-label="top navigation">
        <i data-toggle="wy-nav-top" class="fa fa-bars"></i>
        <a href="../../../index.html">Python Anti-Patterns</a>
      </nav>


      
      <div class="wy-nav-content" id="signup-box" >
        <div class="rst-content">
          <div class="navigation" role="navigation" aria-label="breadcrumbs navigation">
  <ul class="wy-breadcrumbs">
    <li><a href="../../../index.html">Documentation</a> &raquo;</li>
      
          <li><a href="../../index.html"><i class="fa fa-book"></i> Django</a> &raquo;</li>
      
          <li><a href="index.html"><i class="fa fa-dashboard"></i> Performance</a> &raquo;</li>
      
    <li>Inefficient database queries</li>
      <li class="wy-breadcrumbs-aside">
        
          <a href="../../../_sources/django/all/performance/inefficient_database_queries.rst.txt" rel="nofollow"> View page source</a>
        
      </li>
  </ul>
  <hr/>
</div>

          <div role="main">
            
  <div class="section" id="inefficient-database-queries">
<h1>Inefficient database queries<a class="headerlink" href="#inefficient-database-queries" title="Permalink to this headline">¶</a></h1>
<p>Django’s models make it easy for you, to filter the data of your application without using any SQL statements. This is a great thing, however, it sometimes hides that you are using object filters inefficiently. Unless you append <code class="docutils literal notranslate"><span class="pre">.values()</span></code> to your filter, your QuerySet will always query all columns within your database. This can be uncritical until you scale your application or once your tables grow bigger. Therefore, make sure you only retrieve the columns your really need within your program.</p>
<div class="section" id="anti-pattern">
<h2>Anti-Pattern<a class="headerlink" href="#anti-pattern" title="Permalink to this headline">¶</a></h2>
<p>Let’s assume we have a an app <code class="docutils literal notranslate"><span class="pre">vehicle</span></code> which contains a model <code class="docutils literal notranslate"><span class="pre">Cars</span></code> to store plenty of information about a car:</p>
<div class="highlight-python notranslate"><div class="highlight"><pre><span></span><span class="sd">&quot;&quot;&quot; models.py &quot;&quot;&quot;</span>

<span class="k">class</span> <span class="nc">Cars</span><span class="p">(</span><span class="n">models</span><span class="o">.</span><span class="n">Model</span><span class="p">):</span>
    <span class="n">make</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">CharField</span><span class="p">(</span><span class="n">max_length</span><span class="o">=</span><span class="mi">50</span><span class="p">)</span>
    <span class="n">model</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">CharField</span><span class="p">(</span><span class="n">max_length</span><span class="o">=</span><span class="mi">50</span><span class="p">)</span>
    <span class="n">wheels</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">CharField</span><span class="p">(</span><span class="n">max_length</span><span class="o">=</span><span class="mi">2</span><span class="p">)</span>
    <span class="c1"># ...</span>
</pre></div>
</div>
<p>We import this model into one of your views to do something will make names within our database:</p>
<div class="highlight-python notranslate"><div class="highlight"><pre><span></span><span class="sd">&quot;&quot;&quot; views.py &quot;&quot;&quot;</span>
<span class="kn">from</span> <span class="nn">models</span> <span class="kn">import</span> <span class="n">Cars</span>

<span class="c1"># ...</span>

<span class="n">cars</span> <span class="o">=</span> <span class="n">Cars</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<span class="k">for</span> <span class="n">car</span> <span class="ow">in</span> <span class="n">cars</span><span class="p">:</span>
    <span class="n">do_something</span><span class="p">(</span><span class="n">car</span><span class="o">.</span><span class="n">make</span><span class="p">)</span>
</pre></div>
</div>
<p>Even though this code works and looks harmless, it can kill you in production. You think, you are actually just accessing the <code class="docutils literal notranslate"><span class="pre">make</span></code> field, but you are actually retrieving ALL data from your database, once you start iterating over the retrieved QuerySet:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">make</span><span class="p">,</span> <span class="n">model</span><span class="p">,</span> <span class="n">wheels</span><span class="p">,</span> <span class="p">...</span> <span class="k">FROM</span> <span class="n">vehicles_cars</span><span class="p">;</span>
</pre></div>
</div>
<p>Especially, if you have many fields on your model and/or if you got millions of records in your table, this slows down the response time of your applications significantly. As QuerySets are cached upon evaluation, it will hit your database only once, but you’d better be carful.</p>
</div>
<div class="section" id="best-practice">
<h2>Best practice<a class="headerlink" href="#best-practice" title="Permalink to this headline">¶</a></h2>
<div class="section" id="use-values">
<h3>Use <code class="docutils literal notranslate"><span class="pre">.values()</span></code><a class="headerlink" href="#use-values" title="Permalink to this headline">¶</a></h3>
<p>To avoid such a scenario, make sure you only query the data you really need for your program. Use <code class="docutils literal notranslate"><span class="pre">.values()</span></code> to restrict the underlying SQL query to required fields only.</p>
<div class="highlight-python notranslate"><div class="highlight"><pre><span></span><span class="sd">&quot;&quot;&quot; views.py &quot;&quot;&quot;</span>
<span class="kn">from</span> <span class="nn">cars.models</span> <span class="kn">import</span> <span class="n">Cars</span>

<span class="n">cars</span> <span class="o">=</span> <span class="n">Cars</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">all</span><span class="p">()</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="s1">&#39;make&#39;</span><span class="p">)</span>

<span class="c1"># Print all makes</span>
<span class="k">for</span> <span class="n">car</span> <span class="ow">in</span> <span class="n">cars</span><span class="p">:</span>
    <span class="n">do_something</span><span class="p">(</span><span class="n">car</span><span class="p">[</span><span class="s1">&#39;make&#39;</span><span class="p">])</span>
</pre></div>
</div>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">make</span> <span class="k">from</span> <span class="n">vehicles_cars</span><span class="p">;</span>
</pre></div>
</div>
</div>
<div class="section" id="use-values-list">
<h3>Use <code class="docutils literal notranslate"><span class="pre">.values_list()</span></code><a class="headerlink" href="#use-values-list" title="Permalink to this headline">¶</a></h3>
<p>Alternatively, you can use <code class="docutils literal notranslate"><span class="pre">.value_list()</span></code>. It is similar to <code class="docutils literal notranslate"><span class="pre">values()</span></code> except that instead of returning dictionaries, it returns tuples when you iterate over it.</p>
<div class="highlight-python notranslate"><div class="highlight"><pre><span></span><span class="sd">&quot;&quot;&quot; views.py &quot;&quot;&quot;</span>
<span class="kn">from</span> <span class="nn">cars.models</span> <span class="kn">import</span> <span class="n">Cars</span>

<span class="n">cars</span> <span class="o">=</span> <span class="n">Cars</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">all</span><span class="p">()</span><span class="o">.</span><span class="n">values_list</span><span class="p">(</span><span class="s1">&#39;make&#39;</span><span class="p">,</span> <span class="n">flat</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>

<span class="c1"># Print all makes</span>
<span class="k">for</span> <span class="n">make</span> <span class="ow">in</span> <span class="n">cars</span><span class="p">:</span>
    <span class="n">do_something</span><span class="p">(</span><span class="n">make</span><span class="p">)</span>
</pre></div>
</div>
</div>
</div>
<div class="section" id="references">
<h2>References<a class="headerlink" href="#references" title="Permalink to this headline">¶</a></h2>
<ul class="simple">
<li><p><a class="reference external" href="https://docs.djangoproject.com/en/1.8/ref/models/querysets/#values">Django documentation - Models: Querysets (values)</a></p></li>
<li><p><a class="reference external" href="https://docs.djangoproject.com/en/1.8/ref/models/querysets/#values_list">Django documentation - Models: Querysets (values_list)</a></p></li>
</ul>
</div>
</div>


          </div>
          <footer>
  
    <div class="rst-footer-buttons" role="navigation" aria-label="footer navigation">
      
        <a href="../../1.8/migration/index.html" class="btn btn-neutral float-right" title="Migration to 1.8"/>Next <span class="fa fa-arrow-circle-right"></span></a>
      
      
        <a href="index.html" class="btn btn-neutral" title="Performance"><span class="fa fa-arrow-circle-left"></span> Previous</a>
      
    </div>
  

  <hr/>

  <div role="contentinfo">
    <p>
    </p>
  </div>

    <!--End mc_embed_signup-->
  <a href="https://github.com/snide/sphinx_rtd_theme">Sphinx theme</a> provided by <a href="https://readthedocs.org">Read the Docs</a> - Last updated: Sep 29, 2020 
</footer>
        </div>
      </div>

    </section>


  </div>
  


  

    <script type="text/javascript">
        var DOCUMENTATION_OPTIONS = {
            URL_ROOT:'../../../',
            VERSION:'',
            COLLAPSE_INDEX:false,
            FILE_SUFFIX:'.html',
            HAS_SOURCE:  true
        };
    </script>
      <script type="text/javascript" src="../../../_static/jquery.js"></script>
      <script type="text/javascript" src="../../../_static/underscore.js"></script>
      <script type="text/javascript" src="../../../_static/doctools.js"></script>
      <script type="text/javascript" src="../../../_static/language_data.js"></script>

  

  
  
    <script type="text/javascript" src="../../../_static/js/theme.js"></script>
  

  
  
  <script type="text/javascript">
      jQuery(function () {
          SphinxRtdTheme.StickyNav.enable();
      });
  </script>
   

  <!-- Place this tag right after the last button or just before your close body tag. -->
  <script async defer id="github-bjs" src="https://buttons.github.io/buttons.js"></script>

</body>
</html>